﻿Namespace SchoolClick.Database.DAL

    Public Interface IGrados
        ReadOnly Property IGrados As IGrados

        Function GetAll() As IEnumerable(Of GROUP_GRADOS)
        Function GetMaterias(grado As String, año As String) As IEnumerable(Of V_MATERIAS)
        Sub DeleteMateria(Materia As String, Grado As String, Año As String)
        Function GetMateriasExclude(grado As String, año As String) As IEnumerable(Of MATERIA_EXCLUDE_GRADO)
        Sub CrearMateria(materia As String)
        Sub AsociarMateria(Grado As String, Año As String, Materia As Integer)
        Function GetInfoComposicionSeccion(Año As String, GradoEsp As String) As InfoComposicionGradoSeccion
        Sub SaveComposicionGrado(Año As String, GradoEsp As String, Antiguo As Integer, modelo As InfoComposicionGradoSeccion)
        Sub CrearTodasSecciones(Año As Integer)
        Function IsCreateSecciones(Año As String) As Boolean
        Function IsImportAlumnosNuevoIngreso(Año As String) As Boolean
        Function IsAlumnosAsignadosSeccion(Año As String) As Boolean
        Function GetGradosAndSecciones(Año As String) As IEnumerable(Of V_GRADOS_SECCIONES_SYSTEM)
        Function GetAlumnosByIDGrado(Año As String, IDGrado As Integer) As IEnumerable(Of VIEW_ALL_GRADOS_ALUMNOS)
        Function GetMateriasAndProfesor(Año As String, IDGrado As Integer) As IEnumerable(Of V_GRADOS_MATERIA_PROF_IMPARTE)
        Function GetAlumnosInscritosInYear(Año As String) As IEnumerable(Of V_ALUMNOS_INSCRITOS_BY_SECCION)
        Function GetGradosByProfesorImparter(Año As String, Profesor As String) As IEnumerable(Of GRADOS_IMPARTER_PROFESOR)
    End Interface

    Partial Public Class SchoolClickDAL
        Implements IGrados

        Public ReadOnly Property IGrados As IGrados Implements IGrados.IGrados
            Get
                Return DirectCast(Me, IGrados)
            End Get
        End Property

        Private Function GetAll() As IEnumerable(Of GROUP_GRADOS) Implements IGrados.GetAll
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.GROUP_GRADOS.ToArray
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function GetMaterias(grado As String, año As String) As IEnumerable(Of V_MATERIAS) Implements IGrados.GetMaterias
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.V_MATERIAS.Where(Function(m) m.ANIO_ESCOLAR = año AndAlso m.ID = grado) _
                                     .OrderBy(Function(m) m.MATERIA) _
                                     .ToArray
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Sub DeleteMateria(Materia As String, Grado As String, Año As String) Implements IGrados.DeleteMateria
            Dim cnn As New SchoolClickEntities
            Try
                cnn.DEL_MATERIA_GRADO(Año, Materia, Grado, ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Sub

        Private Function GetMateriasExclude(grado As String, año As String) As IEnumerable(Of MATERIA_EXCLUDE_GRADO) Implements IGrados.GetMateriasExclude
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.GET_MATERIAS_EXCLUDE_GRADO(año, grado) _
                                     .OrderBy(Function(m) m.MATERIA) _
                                     .ToArray
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Sub CrearMateria(materia As String) Implements IGrados.CrearMateria
            Dim cnn As New SchoolClickEntities
            Try
                cnn.NEW_MATERIA(materia, ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Sub

        Private Sub AsociarMateria(Grado As String, Año As String, Materia As Integer) Implements IGrados.AsociarMateria
            Dim cnn As New SchoolClickEntities
            Try
                Dim g() = Grado.Split("-"c)
                cnn.ASOCIAR_MATERIA_GRADO(Año, g(0), g(1), Materia,
                                          ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Sub

        Private Function GetInfoComposicionSeccion(Año As String, GradoEsp As String) As InfoComposicionGradoSeccion Implements IGrados.GetInfoComposicionSeccion
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.COMPOSICION_GRADOS_SECCIONES.Where(Function(c) c.ANIO_ESCOLAR = Año And c.ID_GRADOESP = GradoEsp) _
                                                       .Select(Function(c) New InfoComposicionGradoSeccion With {
                                                                   .AlumnosNuevoIngreso = c.NUEVO_INGRESO,
                                                                   .AlumnosXGrado = c.ALUMNOS_X_SECCION
                                                               }).SingleOrDefault
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Sub SaveComposicionGrado(Año As String, GradoEsp As String, Antiguo As Integer, modelo As InfoComposicionGradoSeccion) Implements IGrados.SaveComposicionGrado
            Dim cnn As New SchoolClickEntities
            Try
                Dim g() = GradoEsp.Split("-"c)
                cnn.SAVE_COMPOSICION_GRADOESP_AE(Año, g(0), g(1), modelo.AlumnosNuevoIngreso, modelo.AlumnosXGrado, Antiguo,
                                          ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Sub

        Private Sub CrearTodasSecciones(Año As Integer) Implements IGrados.CrearTodasSecciones
            Dim cnn As New SchoolClickEntities
            Try
                cnn.NEW_SECCIONES_GRADO_ANIO_ESCOLAR(Año, ConfigurationManager.AppSettings("Rol_ERA").ToSecureString.ToStringInseguro)
            Catch ex As EntityCommandExecutionException
                Throw New EntityCommandExecutionException(ex.InnerException.Message, ex)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Sub

        Private Function IsCreateSecciones(Año As String) As Boolean Implements IGrados.IsCreateSecciones
            Dim cnn As New SchoolClickEntities
            Try
                Dim c = cnn.GRADOS.Where(Function(g) g.ANIO_ESCOLAR = Año).Count()
                Return If(c.Equals(0), False, True)
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function IsImportAlumnosNuevoIngreso(Año As String) As Boolean Implements IGrados.IsImportAlumnosNuevoIngreso
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.IS_IMPORT_NUEVO_INGRESO(Año).Single
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function IsAlumnosAsignadosSeccion(Año As String) As Boolean Implements IGrados.IsAlumnosAsignadosSeccion
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.IS_ALUMNOS_ASOCIADOS_EN_SECCIONES(Año).Single
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function GetGradosAndSecciones(Año As String) As IEnumerable(Of V_GRADOS_SECCIONES_SYSTEM) Implements IGrados.GetGradosAndSecciones
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.V_GRADOS_SECCIONES_SYSTEM.Where(Function(gs) gs.ANIO_ESCOLAR = Año).ToArray
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function GetAlumnosByIDGrado(Año As String, IDGrado As Integer) As IEnumerable(Of VIEW_ALL_GRADOS_ALUMNOS) Implements IGrados.GetAlumnosByIDGrado
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.VIEW_ALL_GRADOS_ALUMNOS.Where(Function(g) g.ANIO_ESCOLAR = Año And g.ID_GRADO = IDGrado).ToArray
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function GetMateriasAndProfesor(Año As String, IDGrado As Integer) As IEnumerable(Of V_GRADOS_MATERIA_PROF_IMPARTE) Implements IGrados.GetMateriasAndProfesor
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.V_GRADOS_MATERIA_PROF_IMPARTE.Where(Function(g) g.ANIO_ESCOLAR = Año And g.ID_GRADO = IDGrado).ToArray
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Private Function GetAlumnosInscritosInYear(Año As String) As IEnumerable(Of V_ALUMNOS_INSCRITOS_BY_SECCION) Implements IGrados.GetAlumnosInscritosInYear
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.V_ALUMNOS_INSCRITOS_BY_SECCION.Where(Function(gs) gs.ANIO_ESCOLAR = Año).ToArray
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function

        Public Function GetGradosByProfesorImparter(Año As String, Profesor As String) As IEnumerable(Of GRADOS_IMPARTER_PROFESOR) Implements IGrados.GetGradosByProfesorImparter
            Dim cnn As New SchoolClickEntities
            Try
                Return cnn.GET_GRADOS_IMPARTER_PROFESOR(Año, Profesor) _
                                     .OrderByDescending(Function(m) m.ESPECIALIDAD) _
                                     .ThenBy(Function(g) g.GRADO) _
                                     .ThenBy(Function(g) g.SECCION) _
                                     .ToArray
            Catch ex As Exception
                Throw ex
            Finally
                cnn.Dispose()
            End Try
        End Function
    End Class
End Namespace
